<?php

namespace app\models;

use yii;
use yii\db\Query;

/**
 *客户表
 *
 */
class Customer
{

    private $table;

    public function __construct()
    {
        $this->table = yii::$app->db->tablePrefix . 'customer';
    }

    public function one($field, $where = [])
    {
        $db = new Query();
        $db->select($field)->from($this->table);
        if (!empty($where['id'])) {
            $db->andWhere(['id' => $where['id']]);
        }

        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }

        if (!empty($where['email'])) {
            $db->andWhere(['email' => $where['email']]);
        }

        return $db->one();
    }

    /**
     * @param $array array
     * @return bool
     */
    public function insert($array)
    {
        return yii::$app->db->createCommand()->insert($this->table, $array)->execute();
    }

    //所有客户来源
    public function get_all_customer_refer()
    {
        $db = new Query();
        return $db->select('*')
            ->from(yii::$app->db->tablePrefix . "customer_refer")
            ->all();
    }

    //所有客户状态
    public function get_all_customer_status()
    {
        $db = new Query();
        return $db->select('*')
            ->from(yii::$app->db->tablePrefix . 'customer_status')
            ->all();
    }

    public function get_all_customer_group()
    {
        $db = new Query();
        return $db->select('*')
            ->from(yii::$app->db->tablePrefix . 'customer_group')
            ->all();
    }

    /**更新customer记录
     * @param $data
     * @param $id
     * @return int
     */
    public function updateRecord($data, $id)
    {
        try {
            return yii::$app->db->createCommand()->update($this->table, $data, ['id' => $id])->execute();
        } catch (\Exception $e) {
            yii::error($e->getMessage());
            return false;
        }

    }

    /**
     * @param $data
     * @return int
     */
    public function insert_customer($data)
    {
        $table_key = [
            'number', 'customer_name', 'link_man', 'email', 'follow_man', 'customer_status', 'customer_star', 'customer_group', 'phone', 'refer', 'products', 'country', 'customer_remark', 'linkman_phone', 'linkman_remark',
        ];
        $str = [];
        foreach ($data as $key => $value) {
            if ($value[0]) {
                foreach ($value as $k => $v) {
                    $value[$k] = "\"" . $v . "\"";
                }
                $str[$key] = "(" . implode(",", $value) . ")";
            }
        }

        $table     = yii::$app->db->tablePrefix . "customer";
        $key_str   = implode(',', $table_key);
        $value_str = implode(',', $str);
        $sql       = "insert into " . $table . '(' . $key_str . ')values' . $value_str;
        return yii::$app->db->createCommand($sql)->execute();
    }

    /**创建客户编号
     * @return string
     */
    public function generateNum()
    {
        do {
            $num    = rand(1, 100);
            $num    = str_pad($num, 3, '0', STR_PAD_LEFT);
            $number = 'K' . date('ymd') . $num;
            $db     = new Query();
            $re     = $db->select('id')
                ->from($this->table)
                ->where(['number' => $number])
                ->one();
            if (!$re) {
                return $number;
            }
        } while (!$re);
    }

    /**获取客户list
     * @param array $where
     * @return array
     */
    public function get_all_customer($where = [])
    {
        $db = new Query();
        $db->select('number,customer_name')->from($this->table);
        if (!empty($where['follow_man'])) {
            $db->andWhere(['like', 'follow_man', '"' . $where['follow_man'] . '"']);
        }
        $list = $db->all();
        return $list;
    }

    public function get_customer_list($p = false, $s = false, $where = [])
    {
        $db = (new Query())->select(['*', 'convert(substring(number,2),SIGNED) as orderbynum'])
            ->from($this->table);
        if (!empty($where['property'])) {
            $db->andWhere(['property' => $where['property']]);
        }

        if (!empty($where['refer'])) {
            $db->andWhere(['refer' => $where['refer']]);
        }

        if (!empty($where['property'])) {
            $db->andWhere(['property' => $where['property']]);
        }

        if (!empty($where['customer_group'])) {
            $db->andWhere(['customer_group' => $where['customer_group']]);
        }

        if (!empty($where['customer_status'])) {
            $db->andWhere(['customer_status' => $where['customer_status']]);
        }

        if (!empty($where['email'])) {
            $db->andWhere(['email' => $where['email']]);
        }

        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }

        if (!empty($where['from_website'])) {
            $db->andWhere(['from_website' => $where['from_website']]);
        }

        if (!empty($where['follow_man'])) {
            $db->andWhere(['!=', 'follow_man', '']);
            $db->andWhere("JSON_CONTAINS(follow_man,'\"{$where['follow_man']}\"')");
        }

        if (!empty($where['customer_name'])) {
            $db->andWhere(['LIKE', 'customer_name', $where['customer_name']]);
        }

        if (!empty($where['need_update'])) {
            $db->andWhere(['in', 'id', $where['need_update']]);
        }
        if ($p) {
            $db->offset(($p - 1) * $s);
        }
        if ($s) {
            $db->limit($s);
        }
        return $db->orderBy(['id' => SORT_DESC])->all();
    }

    public function get_customer_size($where = [])
    {
        $db = new Query();
        $db->select('count(id) as num')->from($this->table);

        if (!empty($where['property'])) {
            $db->andWhere(['property' => $where['property']]);
        }

        if (!empty($where['refer'])) {
            $db->andWhere(['refer' => $where['refer']]);
        }

        if (!empty($where['property'])) {
            $db->andWhere(['property' => $where['property']]);
        }

        if (!empty($where['customer_group'])) {
            $db->andWhere(['customer_group' => $where['customer_group']]);
        }

        if (!empty($where['customer_status'])) {
            $db->andWhere(['customer_status' => $where['customer_status']]);
        }

        if (!empty($where['email'])) {
            $db->andWhere(['email' => $where['email']]);
        }

        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }

        if (!empty($where['from_website'])) {
            $db->andWhere(['from_website' => $where['from_website']]);
        }

        if (!empty($where['follow_man'])) {
            $db->andWhere(['!=', 'follow_man', '']);
            $db->andWhere("JSON_CONTAINS(follow_man,'\"{$where['follow_man']}\"')");
        }

        if (!empty($where['customer_name'])) {
            $db->andWhere(['LIKE', 'customer_name', $where['customer_name']]);
        }

        if (!empty($where['need_update'])) {
            $db->andWhere(['in', 'id', $where['need_update']]);
        }

        $re = $db->one();
        return $re ? $re['num'] : 0;
    }

    public function search_num_by_name($name)
    {
        $db   = new Query();
        $info = $db->select("number,customer_name,email")
            ->from($this->table)
            ->where(['like', 'customer_name', "$name"])
            ->one();
        return $info;
    }

    public function search_customer_by_name($name)
    {
        $db   = new Query();
        $info = $db->select("id,number,customer_name,email")
            ->from($this->table)
            ->where(['like', 'customer_name', "$name"])
            ->all();
        return $info;
    }

    //获取一个numer
    public function get_number($number)
    {
        $db = new Query();
        $re = $db->select('*')->from($this->table)->where(['number' => $number])->one();
        if ($re) {
            return true;
        }
        return false;
    }

    public function get_number_by_email($email)
    {
        $db = new Query();
        return $db->select('*')
            ->from($this->table)
            ->where(['email' => $email])
            ->limit(1)
            ->one();
    }

    /**
     * @param int $id
     * @param array $data
     * @return string sql
     */
    public function update_sql($id, $data)
    {
        return yii::$app->db->createCommand()->update($this->table, $data, ['id' => $id])->getRawSql();
    }

    /**
     * 获取最大编号
     * @return int
     */
    public function getMaxNum()
    {
        $db   = new Query();
        $info = $db->select(['convert(substring(number,2),SIGNED) as num'])
            ->from($this->table)
            ->orderBy(['num' => SORT_DESC])
            ->limit(1)
            ->one();
        return $info['num'];
    }

    /**
     * @return int
     */
    public function getMaxNumV2()
    {
        $str  = 'K' . date('ymd') . '%';
        $db   = new Query();
        $info = $db->select('count(id) as num')
            ->from($this->table)
            ->where(['like', 'number', $str, false])
            ->one();
        return $info ? $info['num'] : 0;
    }

    /**
     * 获取一条记录
     * @param string $email
     *
     */
    public function searchByEmail($email)
    {
        $db = new Query();
        return $db->select('*')->from($this->table)->where(['email' => $email])->limit(1)->one();
    }

    /**通过客户编号获取客户名称
     * @param array $arr
     * @return array
     */
    public function getCustomerByNum($arr = [])
    {
        $db   = new Query();
        $list = $db->select('customer_name,number')->from($this->table)->where(['in', 'number', $arr])->all();
        $tmp  = [];
        foreach ($list as $v) {
            $tmp[$v['number']] = $v['customer_name'];
        }
        return $tmp;
    }

    public function getCustomerById($arr = [])
    {
        $db   = new Query();
        $list = $db->select('customer_name,number,id')->from($this->table)
            ->where(['in', 'id', $arr])->all();
        return $list;
    }
}
